package com.itheima.service.impl;

import com.itheima.domain.vo.AddressVO;
import com.itheima.service.AddressService;
import com.itheima.utils.CategoryUtil;
import com.itheima.utils.SqlUtil;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
public class AddressServiceImpl implements AddressService {
    @Override
    public List<AddressVO> category(String category, String city) throws SQLException {
        String tb = CategoryUtil.getCategory(category);
        List<AddressVO> result = query(category, city,tb);
        return result;
    }
    private static List<AddressVO> query(String category, String city,String tb) throws SQLException {
        SqlUtil sqlUtil = new SqlUtil();
        String sql;
        if(tb!="total"){
            sql = "select region as name,sum(count) as value from "+tb+"_address_analysis where city='"+city+"' and category='"+category+"' group by region";
        }
        else {
            sql = "select region as name,sum(count) as value from "+tb+"_address_analysis where city='"+city+"' group by region";
        }
        ResultSet resultSet = sqlUtil.executeQuery(sql);
        List<AddressVO> addressVOS = new ArrayList<>();
        while (resultSet.next()) {
            AddressVO build = AddressVO.builder()
                    .name(resultSet.getString("name"))
                    .value(resultSet.getInt("value"))
                    .build();
            addressVOS.add(build);
        }
        sqlUtil.close();
        return addressVOS;
    }
}
